﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class Phong_DAL : BaseDAL
    {
        public DataTable Select()
        {
            var sql = @"SELECT * FROM phong";
            return ExecuteQuery(sql);
        }
        public DataTable Select_tdn(string _tdn)
        {
            var sql = @"
                        SELECT a.* 
                        FROM phong a, nhan_vien b
                        where a.ma_nv = b.ma_nv
		                        and b.ma_nv = '{0}'
                    ";
            sql = string.Format(sql, _tdn);
            return ExecuteQuery(sql);
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM PHONG WHERE MA_PHONG = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (PHONG)obj;
            string sql = @"set dateformat dmy;INSERT INTO PHONG(MA_PHONG, TEN_PHONG, MA_LP, MA_KV, MA_NV) VALUES('{0}', N'{1}', '{2}', '{3}', '{4}') ";
            sql = string.Format(sql, o.MA_PHONG, o.TEN_PHONG, o.MA_LP, o.MA_KV, o.MA_NV);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (PHONG)obj;
            string sql = @"set dateformat dmy;UPDATE PHONG SET TEN_PHONG=N'{1}', MA_LP='{2}',MA_KV='{3}',MA_NV='{4}'  where MA_PHONG='{0}'";
            sql = string.Format(sql, o.MA_PHONG, o.TEN_PHONG, o.MA_LP, o.MA_KV, o.MA_NV);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Select_max()
        {
            var sql = @"SELECT max(MA_PHONG) as id FROM PHONG";
            return ExecuteQuery(sql);
        }
        //load nv
        public DataTable load_NV()
        {
            string sql = @" 
                            SELECT MA_NV AS id ,HOTEN_NV as ten FROM nhan_vien
                            ";
            return ExecuteQuery(sql);
        }
        //load khu vuc
        public DataTable load_KV()
        {
            string sql = @" 
                            SELECT MA_KV AS id ,TEN_KV as ten FROM khu_vuc
                            ";
            return ExecuteQuery(sql);
        }
        //load loai phong
        public DataTable load_LP()
        {
            string sql = @" 
                            SELECT MA_LP AS id ,TEN_LP as ten FROM loai_phong
                            ";
            return ExecuteQuery(sql);
        }

        //dem so nhan vien
        public string Select_count()
        {
            string sql = @"SELECT COUNT(MA_PHONG) as id FROM PHONG";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
        }

        public DataTable load_phong()
        {
            string sql = @" 
                            SELECT MA_PHONG AS id ,TEN_PHONG as ten FROM PHONG
                            ";
            return ExecuteQuery(sql);
        }
    }
}
